from flask import Blueprint, request, jsonify
from flask_jwt_extended import jwt_required, get_jwt_identity
from app import db
from app.models.sales import Sales
from app.models.car import Car
from app.models.user import User
from app.models.brand import Brand
from sqlalchemy import func, desc, ColumnElement
from sqlalchemy.orm import Query
from sqlalchemy.sql.elements import Label
from datetime import datetime
from typing import Optional, Any, List, Tuple, Union

sales_bp = Blueprint('sales', __name__)

@sales_bp.route('', methods=['GET'])
@jwt_required()
def get_sales() -> Tuple[Any, int]:
    # 获取查询参数
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    car_id = request.args.get('car_id', type=int)
    user_id = request.args.get('user_id', type=int)
    
    # 构建查询
    query: Query = Sales.query
    
    if start_date:
        try:
            start_dt = datetime.strptime(start_date, '%Y-%m-%d')
            # 明确指定类型以避免类型检查错误
            query = query.filter(Sales.sale_date >= start_dt)  # type: ignore
        except ValueError:
            return jsonify({'message': '开始日期格式无效，请使用YYYY-MM-DD格式'}), 400
    
    if end_date:
        try:
            end_dt = datetime.strptime(end_date, '%Y-%m-%d')
            query = query.filter(Sales.sale_date <= end_dt)  # type: ignore
        except ValueError:
            return jsonify({'message': '结束日期格式无效，请使用YYYY-MM-DD格式'}), 400
    
    if car_id:
        query = query.filter(Sales.car_id == car_id)  # type: ignore
    
    if user_id:
        query = query.filter(Sales.user_id == user_id)  # type: ignore
    
    # 执行查询
    sales: List[Sales] = query.all()
    
    return jsonify([sale.to_dict() for sale in sales]), 200

@sales_bp.route('/<int:sale_id>', methods=['GET'])
@jwt_required()
def get_sale(sale_id: int) -> Tuple[Any, int]:
    sale: Optional[Sales] = Sales.query.get(sale_id)
    if not sale:
        return jsonify({'message': '销售记录不存在'}), 404
    return jsonify(sale.to_dict()), 200

@sales_bp.route('', methods=['POST'])
@jwt_required()
def create_sale() -> Tuple[Any, int]:
    current_user_id = get_jwt_identity()
    data = request.get_json()
    
    # 验证必填字段
    if not data.get('car_id') or not data.get('customer_name') or not data.get('unit_price'):
        return jsonify({'message': '车辆ID、客户名称和单价为必填项'}), 400
    
    # 检查车辆是否存在
    car: Optional[Car] = Car.query.get(data['car_id'])
    if not car:
        return jsonify({'message': '车辆不存在'}), 400
    
    # 检查库存
    quantity = int(data.get('quantity', 1))
    if car.stock < quantity:
        return jsonify({'message': '库存不足'}), 400
    
    try:
        # 计算总价
        unit_price = float(data['unit_price'])
        total_price = unit_price * quantity
        
        # 创建销售记录
        sale = Sales(
            car_id=data['car_id'],
            user_id=current_user_id,
            customer_name=data['customer_name'],
            unit_price=unit_price,
            total_price=total_price,
            customer_phone=data.get('customer_phone'),
            customer_address=data.get('customer_address'),
            quantity=quantity,
            sale_date=data.get('sale_date'),
            payment_method=data.get('payment_method'),
            status=data.get('status', 'completed'),
            notes=data.get('notes')
        )
        
        # 更新库存
        car.stock -= quantity
        
        db.session.add(sale)
        db.session.commit()
        
        return jsonify({
            'message': '销售记录创建成功',
            'sale': sale.to_dict()
        }), 201
    except Exception as e:
        db.session.rollback()
        return jsonify({'message': f'创建失败: {str(e)}'}), 500

@sales_bp.route('/statistics', methods=['GET'])
@jwt_required()
def get_sales_statistics() -> Tuple[Any, int]:
    # 获取查询参数
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    
    # 构建查询基础
    stats_query = db.session.query(
        func.sum(Sales.total_price),  # type: ignore
        func.count(Sales.id)  # type: ignore
    )
    
    if start_date:
        try:
            start_dt = datetime.strptime(start_date, '%Y-%m-%d')
            stats_query = stats_query.filter(Sales.sale_date >= start_dt)  # type: ignore
        except ValueError:
            return jsonify({'message': '开始日期格式无效，请使用YYYY-MM-DD格式'}), 400
    
    if end_date:
        try:
            end_dt = datetime.strptime(end_date, '%Y-%m-%d')
            stats_query = stats_query.filter(Sales.sale_date <= end_dt)  # type: ignore
        except ValueError:
            return jsonify({'message': '结束日期格式无效，请使用YYYY-MM-DD格式'}), 400
    
    # 执行查询
    result = stats_query.first()
    
    # 获取销量最高的车型
    top_cars_query = db.session.query(
        Car.id,  # type: ignore
        Car.name,  # type: ignore
        func.sum(Sales.quantity),  # type: ignore
        func.sum(Sales.total_price)  # type: ignore
    ).join(Sales).group_by(Car.id).order_by(desc(func.sum(Sales.quantity))).limit(5)  # type: ignore
    
    top_cars = []
    for row in top_cars_query.all():
        top_cars.append({
            'car_id': row[0],
            'car_name': row[1],
            'total_quantity': int(row[2]) if row[2] else 0,
            'total_revenue': float(row[3]) if row[3] else 0.0
        })
    
    # 获取销售业绩最好的销售人员
    top_salespeople_query = db.session.query(
        User.id,  # type: ignore
        User.nickname,  # type: ignore
        func.count(Sales.id),  # type: ignore
        func.sum(Sales.total_price)  # type: ignore
    ).join(Sales, User.id == Sales.user_id).group_by(User.id).order_by(desc(func.sum(Sales.total_price))).limit(5)  # type: ignore
    
    top_salespeople = []
    for row in top_salespeople_query.all():
        top_salespeople.append({
            'user_id': row[0],
            'nickname': row[1],
            'sales_count': int(row[2]) if row[2] else 0,
            'total_sales': float(row[3]) if row[3] else 0.0
        })
    
    total_sales_value = float(result[0]) if result and result[0] else 0.0
    total_count_value = int(result[1]) if result and result[1] else 0
    
    return jsonify({
        'total_sales': total_sales_value,
        'total_count': total_count_value,
        'top_cars': top_cars,
        'top_salespeople': top_salespeople
    }), 200

@sales_bp.route('/ranking', methods=['GET'])
@jwt_required()
def get_sales_ranking() -> Tuple[Any, int]:
    # 获取查询参数
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    
    # 构建销售人员排行查询
    salespeople_query = db.session.query(
        User.id,  # type: ignore
        User.username,  # type: ignore
        User.nickname,  # type: ignore
        func.count(Sales.id),  # type: ignore
        func.sum(Sales.quantity),  # type: ignore
        func.sum(Sales.total_price)  # type: ignore
    ).join(Sales, User.id == Sales.user_id).group_by(User.id)  # type: ignore
    
    # 构建车型销量排行查询
    cars_query = db.session.query(
        Car.id,  # type: ignore
        Car.name,  # type: ignore
        Brand.name,  # type: ignore
        func.sum(Sales.quantity),  # type: ignore
        func.sum(Sales.total_price)  # type: ignore
    ).join(Sales).join(Brand).group_by(Car.id)  # type: ignore
    
    # 应用日期过滤
    if start_date:
        try:
            start_dt = datetime.strptime(start_date, '%Y-%m-%d')
            salespeople_query = salespeople_query.filter(Sales.sale_date >= start_dt)  # type: ignore
            cars_query = cars_query.filter(Sales.sale_date >= start_dt)  # type: ignore
        except ValueError:
            return jsonify({'message': '开始日期格式无效，请使用YYYY-MM-DD格式'}), 400
    
    if end_date:
        try:
            end_dt = datetime.strptime(end_date, '%Y-%m-%d')
            salespeople_query = salespeople_query.filter(Sales.sale_date <= end_dt)  # type: ignore
            cars_query = cars_query.filter(Sales.sale_date <= end_dt)  # type: ignore
        except ValueError:
            return jsonify({'message': '结束日期格式无效，请使用YYYY-MM-DD格式'}), 400
    
    # 排序并限制结果
    salespeople_result = salespeople_query.order_by(desc(func.sum(Sales.total_price))).all()  # type: ignore
    cars_result = cars_query.order_by(desc(func.sum(Sales.quantity))).all()  # type: ignore
    
    # 格式化结果
    salespeople_ranking = []
    for row in salespeople_result:
        salespeople_ranking.append({
            'user_id': row[0],
            'username': row[1],
            'nickname': row[2],
            'sales_count': int(row[3]) if row[3] else 0,
            'total_quantity': int(row[4]) if row[4] else 0,
            'total_sales': float(row[5]) if row[5] else 0.0
        })
    
    cars_ranking = []
    for row in cars_result:
        cars_ranking.append({
            'car_id': row[0],
            'car_name': row[1],
            'brand_name': row[2],
            'total_quantity': int(row[3]) if row[3] else 0,
            'total_revenue': float(row[4]) if row[4] else 0.0
        })
    
    return jsonify({
        'salespeople_ranking': salespeople_ranking,
        'cars_ranking': cars_ranking
    }), 200